local Db = require "tianku/core/db"

print("\n=== Db Test ===")
print("Version:", Db._VERSION)
print("Lua Version:", _VERSION)

-- 初始化测试数据库
local function init_test_db()
    print("\n[1] Initializing test database...")
    os.execute("rm -f /tmp/thinkorm_test.db")
    os.execute("rm -f /tmp/thinkorm_test2.db")
    
    -- 创建主数据库表
    Db.execute([[
        CREATE TABLE IF NOT EXISTS think_users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE,
            age INTEGER,
            status INTEGER DEFAULT 1,
            created_at INTEGER
        )
    ]])
    print("Created table: think_users")
    
    -- 创建第二个数据库表（确保使用正确的连接名）
    Db.execute([[
        CREATE TABLE IF NOT EXISTS think_orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            amount REAL,
            status INTEGER DEFAULT 0,
            created_at INTEGER,
            FOREIGN KEY(user_id) REFERENCES think_users(id)
        )
    ]], nil, "secondary")
    print("Created table: think_orders in secondary database")
end

 

-- 测试基本CRUD
local function test_basic_crud()
    print("\n[2] Testing Basic CRUD Operations")
    
    local User = Db.name("users")
    
    -- 测试插入
    local user_id = User:data({
        name = "张三",
        email = "zhangsan@example.com",
        age = 25,
        created_at = os.time()
    }):insert()
    print("Inserted user ID:", user_id)
    
    -- 测试查询
    local user = User:where({id = user_id}):find()
    if user then
        print("Found user:", user.name, "| Email:", user.email, "| Age:", user.age)
    else
        print("User not found!")
        return false
    end
    
    -- 测试更新
    local ok = User:where({id = user_id}):data({
        age = 26,
        status = 2
    }):update()
    print("Update result:", ok and "Success" or "Failed")
    
    -- 验证更新
    user = User:where({id = user_id}):find()
    print("Updated age:", user.age)
    
    -- 测试删除
    local affected = User:where({id = user_id}):delete()
    print("Delete affected rows:", affected)
    
    -- 验证删除
    user = User:where({id = user_id}):find()
    print("After delete:", user and "Exists" or "Deleted")
    
    return true
end

-- 测试基本类似 Db::table()
local function test_dbtab_crud()
 

  print("\n=== 测试基本CRUD 带前缀 ===")
    
    -- 插入数据 需要指定完整表名(带前缀)
-- 使用 table() 方法自动添加前缀
    local User = Db.table('think_users')
    
    -- 插入数据
    local user_id = User:data({
        name = "张三",
        email = "zhangsan@example.com",
        age = 25,
        created_at = os.time()
    }):insert()
    
    print("插入用户ID:", user_id)
    -- 需要指定完整表名(带前缀)
local user = Db.table('think_users')
    :where('id', user_id)
    :find()

print(user.name, user_id)

    -- 查询数据
    local user = User:where({id = user_id}):find()
    print("查询用户:", user.name, user.email, user.age)
    
    -- 更新数据
    local affected = User:where({id = user_id}):data({
        age = 26,
        status = 2
    }):update()
    print("更新影响行数:", affected)
    
    -- 验证更新
    user = User:where({id = user_id}):find()
    print("更新后年龄:", user.age)
    
    -- 删除数据
    affected = User:where({id = user_id}):delete()
    print("删除影响行数:", affected)
    
    -- 验证删除
    user = User:where({id = user_id}):find()
    print("删除后查询:", user == nil and "nil" or "存在")

end

-- 测试基本model
local function test_model_crud()
 

  print("\n=== 测试基本modelCRUD带前缀 ===")
    
   

    -- 定义模型
local User = Db.Model("think_users")
  print("\n=== 测试基本modelCRUD 带前缀 ===")
        -- 插入数据 需要指定完整表名(带前缀)
-- 使用 table() 方法自动添加前缀
   -- local User = Db.table('think_users')
    
    -- 插入数据
    local user_id = User:data({
        name = "张三",
        email = "zhangsan@example.com",
        age = 25,
        created_at = os.time()
    }):insert()
    
    print("插入用户ID:", user_id)
    -- 需要指定完整表名(带前缀)
local user = Db.table('think_users')
    :where('id', user_id)
    :find()

print(user.name, user_id)

    -- 查询数据
    local user = User:where({id = user_id}):find()
    print("查询用户:", user.name, user.email, user.age)
    
    -- 更新数据
    local affected = User:where({id = user_id}):data({
        age = 26,
        status = 2
    }):update()
    print("更新影响行数:", affected)
    
    -- 验证更新
    user = User:where({id = user_id}):find()
    print("更新后年龄:", user.age)
    
    -- 删除数据
    affected = User:where({id = user_id}):delete()
    print("删除影响行数:", affected)
    
    -- 验证删除
    user = User:where({id = user_id}):find()
    print("删除后查询:", user == nil and "nil" or "存在")


end



-- 测试事务
local function test_transaction()
    print("\n[3] Testing Transactions")
    
    local User = Db.name("users")
    local Order = Db.table("think_orders", "secondary")
    
    -- 创建测试用户
    local user_id = User:data({
        name = "李四",
        email = "lisi@example.com",
        age = 30,
        created_at = os.time()
    }):insert()
    print("Created user ID:", user_id)
    
    -- 开始事务
    Db.startTrans()
    print("Transaction started")
    
    local ok, err = pcall(function()
        -- 事务操作1: 更新用户
        User:where({id = user_id}):decr("age", 5):update()
        
        -- 事务操作2: 创建订单
        local order_id = Order:data({
            user_id = user_id,
            amount = 100.50,
            created_at = os.time()
        }):insert()
        print("Created order ID:", order_id)
        
        -- 测试错误情况（取消注释测试回滚）
        -- error("Simulated error for rollback test")
        
        Db.commit()
        print("Transaction committed")
    end)
    
    if not ok then
        Db.rollback()
        print("Transaction rolled back:", err)
    end
    
    -- 验证结果
    local user = User:where({id = user_id}):find()
    local orders = Order:where({user_id = user_id}):select()
    
    print("User age:", user.age)
    print("Order count:", #orders)
end

-- 测试多数据库连接
local function test_multi_connection()
    print("\n[4] Testing Multi-Database Connections")
    
    local User = Db.name("users")
    local Order = Db.table("think_orders", "secondary")
    
    -- 插入用户
    local user_id = User:data({
        name = "王五",
        email = "wangwu@example.com",
        age = 35,
        created_at = os.time()
    }):insert()
    print("Created user ID:", user_id)
    
    -- 插入订单
    local order_id = Order:data({
        user_id = user_id,
        amount = 200.75,
        created_at = os.time()
    }):insert()
    print("Created order ID:", order_id)
    
    -- 验证数据
    local user = User:where({id = user_id}):find()
    local order = Order:where({id = order_id}):find()
    
    if user and order then
        print("Verification:")
        print("- User:", user.name)
        print("- Order amount:", order.amount)
        return true
    else
        print("Data verification failed!")
        return false
    end
end

-- 主测试流程
local function run_tests()
    -- 初始化数据库
    init_test_db()
    
    -- 运行测试用例
    local tests = {
        test_basic_crud,
      test_dbtab_crud,
      test_model_crud,
        test_transaction,
        test_multi_connection
    }
    
    for _, test in ipairs(tests) do
        local ok, err = pcall(test)
        if not ok then
            print("\n!!! TEST FAILED !!!")
            print("Error:", err)
            return false
        end
    end
    
    print("\n=== ALL TESTS PASSED ===")
    return true
end

-- 清理测试数据
local function cleanup()
    print("\nCleaning up test data...")
    Db.execute("DROP TABLE IF EXISTS think_users")
    Db.execute("DROP TABLE IF EXISTS think_orders", nil, "secondary")
    print("Cleanup complete")
end

-- 执行测试
run_tests()
cleanup()